<?php
// $Id: Db.php 600 2012-12-14 07:18:47Z tomqin@gmail.com $

/**
 * Part of the framework.
 *
 * @package     Library
 * @version     1.0
 * @copyright   Copyright (C) 2010-2012 SupeFun.com Inc.
 * @author      TomQin <tomqin@gmail.com>
 * @license     http://www.supefun.com/license/
 */

class Db{
    static private $db;

    static public function instance($opts) {
        if(!(self::$db && self::$db instanceof Db_Mysqli)) {
            self::$db = new Db_Mysqli($opts);
            self::$db->dbConncet();
        }
        return self::getInstance();
    }

    static public function getInstance() {
        return self::$db;
    }

    static public function query($sql) {
        try{
            $res = self::$db->query($sql);
        }catch(Exception $e) {
            p($e);
        }
        return $res;
    }

    static public function prepare($sql) {
        return self::$db->prepare($sql);
    }

    static public function getAll($sql) {
        $data = array();
        $result = self::$db->query($sql);
        while($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        return $data;
    }

    static public function getKeyArray($sql, $key1, $key2 = null) {
        $data = array();
        $result = self::$db->query($sql);
        if($key1 && is_string($key2)) {
            while($row = $result->fetch_assoc()) {
                $data[$row[$key1]] = $row[$key2];
            }
        }else{
            while($row = $result->fetch_assoc()) {
                $data[$row[$key1]] = $row;
            }
        }
        return $data;
    }

    static public function getFirst($sql) {
        $row = self::$db->query($sql)->fetch_row();
        return $row[0];
    }

    static public function getRow($sql) {
        return self::$db->query($sql)->fetch_assoc();
    }

    static public function getArray($sql, $type = MYSQLI_ASSOC) {
        return self::$db->query($sql)->fetch_array($type);
    }

    static public function escapeString($var) {
        return self::$db->real_escape_string($var);
    }

    static public function insertId() {
        return self::$db->insert_id;
    }

    static public function affectedRows() {
        return self::$db->affected_rows;
    }

    static public function update($table, array $data, $where) {
        $sql = 'UPDATE '.$table.' SET';
        $comm = '';
        foreach($data as $key => $var) {
            $sql .= $comm . " `{$key}`=" . self::qoute($var);
            $comm = ',';
        }
        $sql .= $where ? ' WHERE '.self::where($where) : '';

        self::query($sql);
        return self::affectedRows();
    }

    static public function insert($table, array $data, $replace = false) {
        $cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';
        $sql = $cmd.' '.$table.' SET';
        $comm = '';
        foreach($data as $key => $var) {
            $sql .= $comm . " `{$key}`=" . self::qoute($var);
            $comm = ',';
        }

        self::query($sql);
        return self::insertId();
    }

    static public function delete($table, $where) {
        $sql = 'DELETE FROM '.$table.' '. ($where ? ' WHERE '.self::where($where) : '');
        self::query($sql);
        return self::affectedRows();
    }

    static public function table($table) {
        return self::$db->getOption('tablepre').$table;
    }

    static public function where($where) {
        if(is_array($where)) {
            $sql = $comm = '';
            foreach($where as $key => $var) {
                $sql .= $comm . "`{$key}` = " . self::qoute($var);
                $comm = ' AND ';
            }
            return $sql;
        }else{
            return $where;
        }
    }

    static public function qoute($var) {
        return is_null($var) ? 'NULL' : "'".self::escapeString($var)."'";
    }

    static public function buildSql($sql) {
        $args = func_get_args();
        array_shift($args);
        foreach($args as $k => $v) {
            $args[$k] = self::escapeString($v);
        }
        return vsprintf($sql, $args);
    }

    /**
     * 执行存储过程专用
     * note: prepare not support mulit query mode (under win32)
     */
    static public function proc($sql, $result_type = MYSQLI_ASSOC) {

        $command = self::getCommand($sql);
        if (!in_array($command, array('CALL'))) {
            throw new Exception('Only `CALL` allowed via Db::proc().');
        }

        $result = self::$db->multi_query($sql);
        if (!$result && self::$db->errno) {
            throw new Exception('Unable to proc a sql.');
        } else if ($result) {
            $data = array();
            do {
                if ($result = self::$db->store_result()) {
                    $rowset = array();
                    while($row = $result->fetch_assoc()) {
                        $rowset[] = $row;
                    }
                    $data[] = $rowset;
                    $result->free();
                }
            } while (self::$db->more_results() && self::$db->next_result());

            return $data;
        } else {
            return ture;
        }
    }

    static public function getCommand($sql) {
        $command = strtoupper(substr($sql, 0, strpos(trim($sql), ' ')));
        return $command;
    }

    static public function getQueries() {
        return self::$db->queries;
    }
}


/**
 * Part of the framework.
 *
 * @package     Library
 * @version     1.0
 * @copyright   Copyright (C) 2010-2012 SupeFun.com Inc.
 * @author      TomQin <tomqin@gmail.com>
 * @license     http://www.supefun.com/license/
 */

class Db_MySqli extends MySqli
{
    protected $options = array();
    protected $queries = array();
    //private $cached_stmt = array();

    public function __construct(array $opts = array()) {
        $this->init_options($opts);

        parent::init(); // init mysqli for options, connection
        mysqli_report(MYSQLI_REPORT_OFF);

        if (!empty($this->options['timeout'])) {
            $ret = parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->options['timeout']);
        }
    }

    public function dbConncet() {
        $ret = parent::real_connect($this->options['host'], $this->options['username'], $this->options['password'],
                                    $this->options['database'], $this->options['port'], $this->options['socket'],
                                    $this->options['flags']);
        if ($ret === false || $this->connect_errno) {
            throw new Exception($this->connect_error, $this->connect_errno);
        }
        if (!empty($this->options['charset'])) {
            parent::set_charset($this->options['charset']);
        }
        return $ret;
    }

    public function __destruct() {
    }

    protected function init_options(array $opts) {
        $this->options = array();
        $this->options['server']     = 'prod'; // prod|qa|dev|etc

        $this->options['host']       = '127.0.0.1';
        $this->options['username']   = null;
        $this->options['password']   = null;
        $this->options['database']   = null;
        $this->options['tablepre']   = '';

        $this->options['port']       = 3306;
        $this->options['socket']     = null; // socket or named pipe
        $this->options['flags']      = null; // null ignore; MYSQLI_CLIENT_COMPRESS, etc

        $this->options['charset']    = null; // null ignore, else utf8, etc
        $this->options['timeout']    = null; // null ignore, else nbr secs

        $this->options = array_merge($this->options, $opts);
    }

    public function getOption($key) {
        return isset($this->options[$key]) ? $this->options[$key] : null;
    }

    public function query($sql) {
        $this->queries[] = $sql;
        $result = parent::query($sql);
        if(!$result || $this->errno) {
            throw new Exception($this->error, $this->errno);
        }else{
            return $result;
        }
    }

    public function prepare($sql) {
        $this->queries[] = $sql;
        return parent::prepare($sql);
    }

    public function stmt_bind_param(&$stmt, $types, $param) {
        $var = array();
        foreach($param as $key => $ref) {
            $var[] = &$param[$key];
        }
        array_unshift($var, $types);
        call_user_func_array(array($stmt, 'bind_param'), $var);
        //$ref = new ReflectionMethod('mysqli_stmt', 'bind_param');
        //$ref->invokeArgs($stmt, $var);
    }

    public function getQueries() {
        return $this->queries;
    }
}
